<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class expenses_model extends CI_Model
{
	
	public function __construct()
	{
		parent::__construct();

	}
public function get_all()
{
$this->datatables
			->select("engineer_expenses.id,case.case_no,users.username,repair.completion_date,engineer_expenses.total,customer.customer_name,customer.mobile")
			->from('engineer_expenses','case','customer','users','repair')
			->join('case','engineer_expenses.case_id=case.id')
			->join('customer','case.service_id=customer.service_id')
			->join('repair','repair.case_id= case.id')
			->join('users','users.id=repair.engineer_id')
			->join('status','repair.status_id=status.id')
			//->where('users.id',$id)
		   // ->where( 'repair.status_id', 10) 
		   
			
		    ->add_column("Actions",
			"<center>		
								<i class='icon-tags'></i>
								
								
								
								
								</center>", "id")
			
			;
			
	   return  $this->datatables->generate();



}	
public function get_all_nonwarranty()
{
$this->datatables
			->select("engineer_expenses.id,case.case_no,users.username,repair.completion_date,engineer_expenses.total,customer.customer_name,customer.mobile")
			->from('engineer_expenses','case','customer','users','repair')
			->join('case','engineer_expenses.case_id=case.id')
			->join('customer','case.service_id=customer.service_id')
			->join('repair','repair.case_id= case.id')
			->join('users','users.id=repair.engineer_id')
			->join('status','repair.status_id=status.id')
			//->where('users.id',$id)
		   // ->where( 'repair.status_id', 10) 
		   
			
		    ->add_column("Actions",
			"<center>		
								<i class='icon-tags'></i>
								
								
								
								
								</center>", "id")
			
			;
			
	   return  $this->datatables->generate();



}	
public function get_all_assigned()
{
$this->datatables
			->select("case.id as id,case.case_no,case.date,users.username,status.name,customer.customer_name")
			->from('case','status','customer','users')
			->join('status','case.status_id= status.id')
			->join('customer','case.service_id=customer.service_id')
			->join('users','users.id=case.engineer_id')
		    ->where('status.id = 1 OR status.id=5')
			
		    ->add_column("Actions",
			"<center>		<a href='index.php/admin/expenses/change_engineer/$1'>
								<i class='icon-tags'></i>
								</a>
								
								
								
								</center>", "id")
			
			;
			
	   return  $this->datatables->generate();

}	
public function get_all_assigned_nonwarranty()
{
$this->datatables
			->select("nonwarranty_case.id as id,nonwarranty_case.case_no,nonwarranty_case.date,users.username,status.name,customer.customer_name")
			->from('nonwarranty_case','status','customer','users')
			->join('status','nonwarranty_case.status_id= status.id')
			->join('customer','nonwarranty_case.service_id=customer.service_id')
			->join('users','users.id=nonwarranty_case.engineer_id')
		    ->where('status.id = 1 OR status.id=5')
			
		    ->add_column("Actions",
			"<center>		<a href='index.php/admin/expenses/change_engineer_nonwarranty/$1'>
								<i class='icon-tags'></i>
								</a>
								
								
								
								</center>", "id")
			
			;
			
	   return  $this->datatables->generate();

}	
public function get_engineer()
	{
	$this->db->select('users.*');
	$this->db->from('users', 'users_groups' );
	$this->db->join('users_groups', 'users.id= users_groups.user_id   '  );
	$this->db->where('users_groups.group_id',2);
	$q = $this->db->get();
		
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}
			return $data;		
		}	
	
public function get_case_detail($id)
{
$query = $this->db->get_where('case', array('id' => $id));
		return $query;

}	
public function get_case_detail_non($id)
{
$query = $this->db->get_where('nonwarranty_case', array('id' => $id));
		return $query;

}
public function chk_admin_assigned($id)
{
$query = $this->db->get_where('users_groups', array('user_id' => $id,'group_id'=>1,));
		return $query;
}
public function update_case($case_id,$case_no,$engineer_id)
{
$insert=array('engineer_id'=>$engineer_id,);
$this->db->update('case',$insert,array('id'=>$case_id,'case_no'=>$case_no,));

}
public function update_diagnosis($case_id,$case_no,$engineer_id)
{
$diagnosis= $this->db->get_where('diagnosis', array('case_id' =>$case_id))->num_rows();	
if($diagnosis != 0)
{
$insert=array('enginer_id'=>$engineer_id,);
$this->db->update('diagnosis',$insert,array('case_id'=>$case_id,));
}else{ }
}
public function update_repair($case_id,$case_no,$engineer_id)
{
$repair= $this->db->get_where('repair', array('case_id' =>$case_id))->num_rows();	
if( $repair != 0)
{
$insert=array('engineer_id'=>$engineer_id,);
$this->db->update('repair',$insert,array('case_id'=>$case_id,));
}else {  }

}
public function update_nonwarranty_case($case_id,$case_no,$engineer_id)
{
$insert=array('engineer_id'=>$engineer_id,);
$this->db->update('nonwarranty_case',$insert,array('id'=>$case_id,'case_no'=>$case_no,));

}
public function update_nonwarranty_diagnosis($case_id,$case_no,$engineer_id)
{
$diagnosis= $this->db->get_where('nonwarranty_diagnosis', array('case_id' =>$case_id))->num_rows();	
if($diagnosis != 0)
{
$insert=array('engineer_id'=>$engineer_id,);
$this->db->update('nonwarranty_diagnosis',$insert,array('case_id'=>$case_id,));
}else{ }
}
public function update_nonwarranty_repair($case_id,$case_no,$engineer_id)
{
$repair= $this->db->get_where('nonwarranty_repair', array('case_id' =>$case_id))->num_rows();	
if( $repair != 0)
{
$insert=array('engineer_id'=>$engineer_id,);
$this->db->update('nonwarranty_repair',$insert,array('case_id'=>$case_id,));
}else {  }

}
public function warranty_report($from_date,$to_date)
{
if($from_date==$to_date)
{

$this->db->select('case.*,users.username,status.name as status');
$this->db->from('case','users','status');
$this->db->join('users','users.id=case.engineer_id');
$this->db->join('status','status.id=case.status_id');
$where = "case.date = '$from_date'";
$this->db->where($where);
$all_case=$this->db->get()->result();
return $all_case;
}else{

$this->db->select('case.*,users.username,status.name as status');
$this->db->from('case','users','status');
$this->db->join('users','users.id=case.engineer_id');
$this->db->join('status','status.id=case.status_id');
$where = "date BETWEEN  '$from_date' AND '$to_date'  ";
$this->db->where($where);
$all_case=$this->db->get()->result();
return $all_case;
}
}
public function nonwarranty_report($from_date,$to_date)
{
if($from_date==$to_date)
{
$this->db->select('nonwarranty_case.*,users.username,status.name as status');
$this->db->from('nonwarranty_case','users','status');
$this->db->join('users','users.id=nonwarranty_case.engineer_id');
$this->db->join('status','status.id=nonwarranty_case.status_id');
$where = "nonwarranty_case.date = '$from_date'";
$this->db->where($where);
$all_case=$this->db->get()->result();
return $all_case;

}else{
$this->db->select('nonwarranty_case.*,users.username,status.name as status');
$this->db->from('nonwarranty_case','users','status');
$this->db->join('users','users.id=nonwarranty_case.engineer_id');
$this->db->join('status','status.id=nonwarranty_case.status_id');
$where = "date BETWEEN  '$from_date' AND '$to_date' ";
$this->db->where($where);
$all_case=$this->db->get()->result();
return $all_case;
}
}
public function get_users()
{
$this->db->select('users.*');
$this->db->from('users','users_groups');
$this->db->join('users_groups','users_groups.user_id=users.id');
$this->db->where('users_groups.group_id',2);
return $this->db->get()->result();

}
public function get_case($id)
{
$date=date('Y/m/d'); 
$this->db->select('case.*,status.id as stat,status.name as warranty_status');
$this->db->from('case','status');
$this->db->join('status','case.status_id=status.id');
$this->db->where('case.engineer_id',$id);
$this->db->where('case.date',$date);
return $this->db->get()->result();
}
public function get_case_date($id,$date)
{
$this->db->select('case.*,status.id as stat,status.name as warranty_status');
$this->db->from('case','status');
$this->db->join('status','case.status_id=status.id');
$this->db->where('case.engineer_id',$id);
$this->db->where('case.date',$date);
return $this->db->get()->result();
}		
public function get_nonwarranty_case($id)
{
$date=date('Y/m/d'); 
$this->db->select('nonwarranty_case.*,status.id as non_stat,status.name as non_status');
$this->db->from('nonwarranty_case','status');
$this->db->join('status','nonwarranty_case.status_id=status.id');
$this->db->where('nonwarranty_case.engineer_id',$id);
$this->db->where('nonwarranty_case.date',$date);
return $this->db->get()->result();
}
public function get_nonwarranty_case_date($id,$date)
{
$this->db->select('nonwarranty_case.*,status.id as non_stat,status.name as non_status');
$this->db->from('nonwarranty_case','status');
$this->db->join('status','nonwarranty_case.status_id=status.id');
$this->db->where('nonwarranty_case.engineer_id',$id);
$this->db->where('nonwarranty_case.date',$date);
return $this->db->get()->result();
}
public function insert_scan($insert)
{
$this->db->insert('scan_receipt',$insert);
}
public function insert_nonwarranty_scan($insert)
{

$this->db->insert('scan_receipt_nonwarranty',$insert);
}
public function update_case_deliver($id)
{
$insert=array('status_id'=>23,);
$this->db->update('case',$insert,array('id'=>$id));
}
public function update_nonwarranty_case_deliver($id)
{
$insert=array('status_id'=>12,);
$this->db->update('nonwarranty_case',$insert,array('id'=>$id));
}
public function get_completed()
{
$this->datatables
			->select("case.id as id,case.case_no,customer.customer_name,customer.mobile,scan_receipt.date,users.username")
			->from('case','scan_receipt','customer','users','status')
			->join('scan_receipt','scan_receipt.case_id=case.id')
			->join('status','case.status_id=status.id')
			->join('customer','case.service_id=customer.service_id')
			->join('users','users.id=case.engineer_id')
		    ->where('case.status_id',23)
			
		    ->add_column("Actions",
			"<center>		<a href='index.php/admin/expenses/view_scan_copy/$1'>
								<i class='icon-tags' title='Scan Copy'></i>
								</a>
								
			</center>", "id");
			
	   return  $this->datatables->generate();

}	
public function nonwarranty_completed()
{
$this->datatables
			->select("nonwarranty_case.id as id,nonwarranty_case.case_no,customer.customer_name,customer.mobile,scan_receipt_nonwarranty.date,users.username")
			->from('nonwarranty_case','scan_receipt_nonwarranty','customer','users','status_nonwarranty')
			->join('scan_receipt_nonwarranty','scan_receipt_nonwarranty.case_id=nonwarranty_case.id')
			->join('status_nonwarranty','nonwarranty_case.status_id=status_nonwarranty.id')
			->join('customer','nonwarranty_case.service_id=customer.service_id')
			->join('users','users.id=nonwarranty_case.engineer_id')
		    ->where('nonwarranty_case.status_id',12)
			
		    ->add_column("Actions",
			"<center>		<a href='index.php/admin/expenses/view_scan_copy_nonwarranty/$1'>
								<i class='icon-tags' title='Scan Copy'></i>
								</a>
								
			</center>", "id");
			
	   return  $this->datatables->generate();

}	

public function get_customer_scan($id)
{
$query = $this->db->get_where('scan_receipt', array('case_id' => $id));
		return $query;
}
public function get_customer_scan_nonwarranty($id)
{
$query = $this->db->get_where('scan_receipt_nonwarranty', array('case_id' => $id));
		return $query;

}
public function get_expense_detail($id)
{
$query = $this->db->get_where('engineer_expenses',array('case_id' =>$id));
		return $query;
}
public function get_expense_detail_nonwarranty($id)
{
$query = $this->db->get_where('nonwarranty_engineer_expenses',array('case_id' =>$id));
		return $query;
}




			
}

?>	